﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for StationGateway
/// </summary>
public class StationGateway
{
    //private string connectionString = "Data Source = ealdb1.eal.local,"

    private DBHelper dbHelper;

    public StationGateway(DBHelper dbHelper)
    {
        this.dbHelper = dbHelper;
    }

    public bool AddStation(int id, string ipAdress, int port, string condition, int status)
    {
        string sql = "insert into station(id, ipAdress, port,condition, status) values (@id, @ipAdress, @port,@condition, @status)";
        return ExecuteNonQuery(sql, CommandType.Text);

    }

    public bool AddPump(int id, decimal capacity, int status, int stationId)
    {
        string sql = "insert into station(id, capacity,status,stationId) values (@id, @capacity, @status,@stationId)";
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public bool EditStation(int id, string ipAdress, int port, string condition, int status)
    {
        string sql = "update station set ipAdress = " + ipAdress + ",port = " + port + ",condition = " + condition + ", status =" + status
        + "where id = " + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public bool EditPump(int id, decimal capacity, int status, int stationId)
    {
        string sql = "update station set capacity = " + capacity + ", status =" + status + "stationId =" + stationId
        + "where id = " + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public bool RemovePump(int id)
    {
        string sql = "delete from Pump where id =" + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public bool RemoveStation(int id)
    {
        string sql = "delete from Pump where id =" + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public void RequestCurrentWaterLevel()
    {
        throw new NotImplementedException();
    }

    public bool StartPump(int id)
    {
        string sql = "update Pump set status = 1 where id =" + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public bool StopPump(int id)
    {
        string sql = "update Pump set status = 0 where id =" + id;
        return ExecuteNonQuery(sql, CommandType.Text);
    }

    public List<Station> RequestInfoForStations()
    {
        string sql = "select * from station";
        Station station;
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        List<Station> stations = new List<Station>();
        foreach (DataRow row in table.Rows)
        {
            station = new Station();
            station.Id = (int) row["id"];
            station.Port = (int) row["port"];
            station.IPAdresse = (string) row["ipAdress"];
            station.Status = (int) row["status"];
            stations.Add(station);
        }
        return stations;
    }

    public List<Pump> GetPump(int stationid)
    {
        Pump pump = null;
        string sql = "select * from Pump where stationId = " + stationid;
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        List<Pump> pumps = new List<Pump>();
        foreach (DataRow row in table.Rows)
        {
            pump = new Pump();
            pump.Id = (int) row["id"];
            pump.Capacity = (double) row["capacity"];
            pump.Status = (int) row["status"];
            pumps.Add(pump);
        }
        return pumps;
    }
    public bool ExecuteNonQuery(string sql, CommandType commandType, params object[] pars)
    {
        bool result = false;
        dbHelper.Open();
        dbHelper.Connect();
        result = dbHelper.ExcuteNonQuery(sql, commandType, pars);
        dbHelper.Close();
        return result;
    }

    public DataTable ExcuteQuery(string sql, CommandType commandType, params object[] pars)
    {
        DataTable table = null;
        dbHelper.Open();
        table = dbHelper.ExcuteQuery(sql, commandType, pars);
        dbHelper.Close();
        return table;
    }


}